package gui;

import gui.events.CreateDatabaseEvent;
import gui.events.CreateTableEvent;
import gui.events.DatabasePopupEvent;
import gui.events.TablePopupEvent;
import gui.interfaces.ICreateDatabaseEventListener;
import gui.interfaces.ICreateTableEventListener;
import gui.interfaces.IPopupMenuDatabaseEventListener;
import gui.interfaces.IPopupMenuTableEventListener;
import gui.renderers.DBMSTreeCellRenderer;
import gui.renderers.TableCellListener;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.EventQueue;
import java.awt.Font;
import java.awt.GridLayout;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.io.IOException;
import java.net.UnknownHostException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Vector;

import javax.swing.AbstractAction;
import javax.swing.Action;
import javax.swing.DefaultListModel;
import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JList;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JSeparator;
import javax.swing.JSplitPane;
import javax.swing.JTabbedPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JToolBar;
import javax.swing.JTree;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableModel;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.DefaultTreeModel;
import javax.swing.tree.TreePath;
import javax.swing.JFileChooser;

import controller.DatabaseController;
import controller.EQueryType;
import controller.InitiliazeController;
import controller.QueryController;
import controller.TableController;
import dal.JMLDatabase;
import dto.ColumnDTO;
import dto.DatabaseIconDTO;
import dto.RowDTO;
import dto.TableIconDTO;
import java.io.File;
import java.io.FileWriter;
import java.io.BufferedWriter;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileNotFoundException;

public class DBMS_Client extends JFrame {

	private JPanel contentPane;
	public static final DefaultMutableTreeNode mockNode = new DefaultMutableTreeNode(
			"Mock Node");
	private static DatabaseIconDTO defaultDatabase = new DatabaseIconDTO("");
	private static DefaultMutableTreeNode defaultDBNode = new DefaultMutableTreeNode(
			new DatabaseIconDTO(""));

	// Cache database and table name to save data after edit table data.
	private static String databaseName = "";
	private static String tableName = "";

	private JPanel panel = new JPanel();
	JPanel tabOutput = new JPanel();
	JPanel tabResult = new JPanel();
	JTabbedPane tabbedPaneOutput = new JTabbedPane(JTabbedPane.TOP);
	JPanel panelQuery1 = new JPanel();
	final JTabbedPane tabbedPaneQueries = new JTabbedPane(JTabbedPane.TOP);
	JSplitPane splitPane_2 = new JSplitPane();
	JTextArea textArea = new JTextArea();
	final JTree treeDBs;
	JSplitPane splitPane_1 = new JSplitPane();
	JSplitPane splitPane = new JSplitPane();
	JButton btnExecuteQuery = new JButton("");
	JButton btnSave = new JButton("");
	JButton btnOpen = new JButton("");
	JButton btnCreateTable = new JButton("");
	final JTextArea txtAreaQueryEditor = new JTextArea();
	private JTable tblResult;
	JList<Object> listOutput = new JList<Object>();
	DefaultListModel<Object> listOutputModel = new DefaultListModel<>();
	JToolBar toolbarResult = new JToolBar();

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					// Using Look-and-Feel Windows for this application.
					try {
						for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager
								.getInstalledLookAndFeels()) {
							if ("Windows".equals(info.getName())) {
								javax.swing.UIManager.setLookAndFeel(info
										.getClassName());
								break;
							}
						}
					} catch (ClassNotFoundException ex) {
						java.util.logging.Logger
								.getLogger(DBMS_Client.class.getName()).log(
										java.util.logging.Level.SEVERE, null,
										ex);
					} catch (InstantiationException ex) {
						java.util.logging.Logger
								.getLogger(DBMS_Client.class.getName()).log(
										java.util.logging.Level.SEVERE, null,
										ex);
					} catch (IllegalAccessException ex) {
						java.util.logging.Logger
								.getLogger(DBMS_Client.class.getName()).log(
										java.util.logging.Level.SEVERE, null,
										ex);
					} catch (javax.swing.UnsupportedLookAndFeelException ex) {
						java.util.logging.Logger
								.getLogger(DBMS_Client.class.getName()).log(
										java.util.logging.Level.SEVERE, null,
										ex);
					}

					Login loginFrame = new Login();
					loginFrame.setVisible(true);
					if (!loginFrame.getIsLogin()) {
						System.exit(0);
					}

					JMLDatabase dao = new JMLDatabase();
					dao.connectToServer("127.0.0.1", 9090);
					DBMS_Client frame = new DBMS_Client();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	/**
	 * @throws IOException
	 * @throws UnknownHostException
	 * 
	 */
	public DBMS_Client() throws IOException {

		addWindowListener(new WindowAdapter() {
			@Override
			public void windowClosing(WindowEvent arg0) {
				System.out.println("closing");
				try {
					JMLDatabase dao = new JMLDatabase();
					dao.logout();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
		setTitle("DBMS Workbench Client");
		setIconImage(Toolkit.getDefaultToolkit().getImage(
				DBMS_Client.class.getResource("images/client-icon.png")));
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 802, 497);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(new BorderLayout(0, 0));

		contentPane.add(panel, BorderLayout.NORTH);
		panel.setLayout(new GridLayout(0, 1, 0, 0));

		JMenuBar menuBar = new JMenuBar();
		menuBar.setBorderPainted(false);
		menuBar.setFont(new Font("Segoe UI", Font.PLAIN, 12));
		panel.add(menuBar);

		JMenu mnFile = new JMenu("File");
		menuBar.add(mnFile);

		JMenuItem mntmNewDatabase = new JMenuItem("New Database");
		mntmNewDatabase.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("New Database");
				// Button Create database's clicked.
				final PanelCreateDatabase panelCreateDatabase = new PanelCreateDatabase();
				panelCreateDatabase
						.addCreateDatabaseEventListener(new ICreateDatabaseEventListener() {
							@Override
							public void cancel(CreateDatabaseEvent e) {
								tabbedPaneQueries.remove(panelCreateDatabase);
							}

							@Override
							public void apply(CreateDatabaseEvent e) {
								// Create database with name
								DatabaseController ctrl = null;
								try {
									ctrl = new DatabaseController(
											panelCreateDatabase
													.getDatbaseName());
								} catch (UnknownHostException e2) {
									// TODO Auto-generated catch block
									e2.printStackTrace();
									JOptionPane.showMessageDialog(
											tabbedPaneQueries, e2.getMessage());
									return;
								} catch (IOException e2) {
									// TODO Auto-generated catch block
									e2.printStackTrace();
									JOptionPane.showMessageDialog(
											tabbedPaneQueries, e2.getMessage());
									return;
								}
								boolean result;
								try {
									result = ctrl.createDatabase();
									if (result) {
										JOptionPane.showMessageDialog(
												panelCreateDatabase,
												"Create successful");
										tabbedPaneQueries
												.remove(panelCreateDatabase);
										reloadDatabaseToTreeView();
									}
								} catch (Exception e1) {
									e1.printStackTrace();
									JOptionPane.showMessageDialog(
											panelCreateDatabase,
											e1.getMessage());
								}
							}
						});
				panelCreateDatabase.setVisible(true);
				tabbedPaneQueries.addTab("Create database", null,
						panelCreateDatabase, "Create database");
				tabbedPaneQueries.setSelectedComponent(panelCreateDatabase);
			}
		});
		mnFile.add(mntmNewDatabase);

		JMenuItem mntmNewQuery = new JMenuItem("New Query");
		mntmNewQuery.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("New Query");
			}
		});
		mnFile.add(mntmNewQuery);

		JMenuItem mntmOpenSqlScript = new JMenuItem("Open SQL Script");
		mntmOpenSqlScript.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("Open SQL Script");
				JFileChooser fc = new JFileChooser();
		        fc.setCurrentDirectory(new File(File.listRoots()[0].getPath()));		        
		        fc.showOpenDialog(null);
		        File selFile = fc.getSelectedFile();
		        if(selFile != null){
		        	String text = "";
		        	FileReader fr;
					try {
						fr = new FileReader(selFile);
						BufferedReader br = new BufferedReader(fr);
			        	String fileLine="";
		                while ((fileLine = br.readLine()) != null) {
		                    text = text + fileLine + "\n";
		                }
		                br.close();
		                txtAreaQueryEditor.setText(text);
					} catch (FileNotFoundException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								e1.getMessage());
					} catch (IOException e2) {
						// TODO Auto-generated catch block
						e2.printStackTrace();
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								e2.getMessage());
					}
		        	
		        }
			}
		});
		mnFile.add(mntmOpenSqlScript);

		JSeparator separator = new JSeparator();
		mnFile.add(separator);

		JMenuItem mntmCloseConnection = new JMenuItem("Close connection");
		mntmCloseConnection.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("Close connection");
				JMLDatabase dao;
				try {
					dao = new JMLDatabase();
					try {
						dao.logout();
					} catch (Exception e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								e1.getMessage());
					}
				} catch (UnknownHostException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							e1.getMessage());
				} catch (IOException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							e1.getMessage());
				}
			}
		});
		mnFile.add(mntmCloseConnection);

		JMenuItem mntmCloseTab = new JMenuItem("Close Tab");
		mntmCloseTab.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("Close Tab");
			}
		});
		mnFile.add(mntmCloseTab);

		JSeparator separator_1 = new JSeparator();
		mnFile.add(separator_1);

		JMenuItem mntmSaveScript = new JMenuItem("Save script");
		mntmSaveScript.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("Save script");
			}
		});
		mnFile.add(mntmSaveScript);

		JMenuItem mntmSaveScriptAs = new JMenuItem("Save script as");
		mntmSaveScriptAs.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("Save script as");
				JFileChooser fc = new JFileChooser();
		        fc.setCurrentDirectory(new File(File.listRoots()[0].getPath()));		        
		        fc.showSaveDialog(null);
		        File selFile = fc.getSelectedFile();
		        if(selFile!= null){
		        	String text = txtAreaQueryEditor.getText();
		        	String[] textArr = text.split("\n");
		        	FileWriter fw;
					try {
						fw = new FileWriter(selFile);
						BufferedWriter bf = new BufferedWriter(fw);
						for(int i =0; i< textArr.length; i++){
							bf.write(textArr[i]);
							bf.newLine();
						}			        	
			        	bf.close();
					} catch (IOException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								e1.getMessage());
					}
		        	
		        }
			}
		});
		mnFile.add(mntmSaveScriptAs);

		JSeparator separator_2 = new JSeparator();
		mnFile.add(separator_2);

		JMenuItem mntmExit = new JMenuItem("Exit");
		mntmExit.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("Exit");
				JMLDatabase dao;
				try {
					dao = new JMLDatabase();
					try {
						dao.logout();
					} catch (Exception e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								e1.getMessage());
					}
				} catch (UnknownHostException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							e1.getMessage());
				} catch (IOException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							e1.getMessage());
				}
				System.exit(0);
			}
		});
		mnFile.add(mntmExit);

		JMenu mnQuery = new JMenu("Query");
		menuBar.add(mnQuery);

		JMenuItem mntmNewQuery_1 = new JMenuItem("New Query");
		mntmNewQuery_1.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("New Query");
			}
		});
		mnQuery.add(mntmNewQuery_1);

		JMenuItem mntmExcuteQuery = new JMenuItem("Excute query");
		mntmExcuteQuery.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("Excute Query");
				String query = txtAreaQueryEditor.getText();
				try {
					executeQuery(query);
					// add to output panel
					addOutputItem(query);
				} catch (Exception e1) {
					System.out.println(e1.getMessage());
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							e1.getMessage());
				}
			}
		});
		mnQuery.add(mntmExcuteQuery);

		JMenu mnDatabase = new JMenu("Database");
		menuBar.add(mnDatabase);

		JMenuItem mntmNewDatabase_1 = new JMenuItem("New Database");
		mntmNewDatabase_1.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("New Database");
				// Button Create database's clicked.
				final PanelCreateDatabase panelCreateDatabase = new PanelCreateDatabase();
				panelCreateDatabase
						.addCreateDatabaseEventListener(new ICreateDatabaseEventListener() {
							@Override
							public void cancel(CreateDatabaseEvent e) {
								tabbedPaneQueries.remove(panelCreateDatabase);
							}

							@Override
							public void apply(CreateDatabaseEvent e) {
								// Create database with name
								DatabaseController ctrl = null;
								try {
									ctrl = new DatabaseController(
											panelCreateDatabase
													.getDatbaseName());
								} catch (UnknownHostException e2) {
									// TODO Auto-generated catch block
									e2.printStackTrace();
									JOptionPane.showMessageDialog(
											tabbedPaneQueries, e2.getMessage());
									return;
								} catch (IOException e2) {
									// TODO Auto-generated catch block
									e2.printStackTrace();
									JOptionPane.showMessageDialog(
											tabbedPaneQueries, e2.getMessage());
									return;
								}
								boolean result;
								try {
									result = ctrl.createDatabase();
									if (result) {
										JOptionPane.showMessageDialog(
												panelCreateDatabase,
												"Create successful");
										tabbedPaneQueries
												.remove(panelCreateDatabase);
										reloadDatabaseToTreeView();
									}
								} catch (Exception e1) {
									e1.printStackTrace();
									JOptionPane.showMessageDialog(
											panelCreateDatabase,
											e1.getMessage());
								}
							}
						});
				panelCreateDatabase.setVisible(true);
				tabbedPaneQueries.addTab("Create database", null,
						panelCreateDatabase, "Create database");
				tabbedPaneQueries.setSelectedComponent(panelCreateDatabase);
			}
		});
		mnDatabase.add(mntmNewDatabase_1);

		JMenuItem mntmDropDatabase = new JMenuItem("Drop database");
		mntmDropDatabase.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("Drop database");
				DefaultMutableTreeNode dbnode = (DefaultMutableTreeNode) treeDBs
						.getLastSelectedPathComponent();
				String dbname = ((DatabaseIconDTO) dbnode.getUserObject())
						.getDatabaseName();
				try {

					DatabaseController ctrl = new DatabaseController(dbname);
					if (ctrl.dropDatabase() == true) {
						DefaultMutableTreeNode rootNode = (DefaultMutableTreeNode) dbnode
								.getParent();
						rootNode.remove(dbnode);
						reloadDatabaseToTreeView();
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								"Drop successful");
					}
				} catch (Exception e2) {
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							"Drop unsuccessful");
				}
			}
		});
		mnDatabase.add(mntmDropDatabase);

		JSeparator separator_4 = new JSeparator();
		mnDatabase.add(separator_4);

		JMenuItem mntmQueryDatabase = new JMenuItem("Query database");
		mntmQueryDatabase.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("Query database");
				DefaultMutableTreeNode dbnode = (DefaultMutableTreeNode) treeDBs
						.getLastSelectedPathComponent();
				// unset default to current default database
				defaultDatabase.setAsDefault(false);
				defaultDatabase = (DatabaseIconDTO) dbnode.getUserObject();
				DatabaseController dbCtrl;
				try {
					dbCtrl = new DatabaseController(defaultDatabase
							.getDatabaseName());
				} catch (UnknownHostException e2) {
					// TODO Auto-generated catch block
					e2.printStackTrace();
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							e2.getMessage());
					return;

				} catch (IOException e2) {
					// TODO Auto-generated catch block
					e2.printStackTrace();
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							e2.getMessage());
					return;
				}
				try {
					dbCtrl.setAsDefault();
					// set default database tree node
					defaultDBNode = dbnode;
				} catch (Exception e1) {
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							e1.getMessage());
				}
				// set default to selected database
				defaultDatabase.setAsDefault(true);
				treeDBs.setCellRenderer(new DBMSTreeCellRenderer());
			}
		});
		mnDatabase.add(mntmQueryDatabase);

		JMenu mnHelp = new JMenu("Help");
		menuBar.add(mnHelp);

		JMenuItem mntmAbout = new JMenuItem("About");
		mntmAbout.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseReleased(MouseEvent e) {
				System.out.println("About");
			}
		});
		mnHelp.add(mntmAbout);

		JToolBar toolBar = new JToolBar();
		toolBar.setFloatable(false);
		panel.add(toolBar);

		// Button create database
		JButton btnCreateDatabase = new JButton("");
		btnCreateDatabase.setToolTipText("New database");
		btnCreateDatabase.setIcon(new ImageIcon(DBMS_Client.class
				.getResource("images/new-database-icon.png")));
		toolBar.add(btnCreateDatabase);

		// Create Table
		btnCreateTable.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseClicked(MouseEvent arg0) {
				// Button create table's clicked.
				final PanelCreateTable panelCreateTable = new PanelCreateTable();
				panelCreateTable
						.addCreateTableEventListener(new ICreateTableEventListener() {

							@Override
							public void cancel(CreateTableEvent e) {
								// Create is canceled.
								tabbedPaneQueries.remove(panelCreateTable);
							}

							@Override
							public void apply(CreateTableEvent e,
									String dbName, String tableName,
									ArrayList<ColumnDTO> cols) {

								try {
									TableController tableCtrl = new TableController(
											dbName, tableName, cols);
									if (tableCtrl.createTable()) {
										JOptionPane.showMessageDialog(
												tabbedPaneQueries,
												"Create table successful");
										tabbedPaneQueries
												.remove(panelCreateTable);
										reloadTableToTreeView(defaultDBNode);
									}
								} catch (Exception e1) {
									JOptionPane.showMessageDialog(
											tabbedPaneQueries, e1.getMessage());
								}
							}
						});
				tabbedPaneQueries.addTab("new_table", null, panelCreateTable,
						"Create table");
				tabbedPaneQueries.setSelectedComponent(panelCreateTable);
			}
		});
		btnCreateTable.setToolTipText("Create a new table to active database");
		btnCreateTable.setIcon(new ImageIcon(DBMS_Client.class
				.getResource("images/new-table-icon.png")));
		toolBar.add(btnCreateTable);

		/*
		 * OPEN SCRIPT FILE
		 */
		btnOpen.setToolTipText("Open a script file");
		btnOpen.setIcon(new ImageIcon(DBMS_Client.class.getResource("images/open.png")));
		btnOpen.addMouseListener(new MouseAdapter() {

			@Override
			public void mouseClicked(MouseEvent e) {
				System.out.println("Open script file");
				JFileChooser fc = new JFileChooser();
		        fc.setCurrentDirectory(new File(File.listRoots()[0].getPath()));		        
		        fc.showOpenDialog(null);
		        File selFile = fc.getSelectedFile();
		        if(selFile != null){
		        	String text = "";
		        	FileReader fr;
					try {
						fr = new FileReader(selFile);
						BufferedReader br = new BufferedReader(fr);
			        	String fileLine="";
		                while ((fileLine = br.readLine()) != null) {
		                    text = text + fileLine + "\n";
		                }
		                br.close();
		                txtAreaQueryEditor.setText(text);
					} catch (FileNotFoundException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								e1.getMessage());
					} catch (IOException e2) {
						// TODO Auto-generated catch block
						e2.printStackTrace();
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								e2.getMessage());
					}
		        	
		        }
			}
		});
		toolBar.add(btnOpen);

		btnSave.setToolTipText("Save script to a file");
		btnSave.setIcon(new ImageIcon(DBMS_Client.class
				.getResource("images/save-icon.png")));
		btnSave.addMouseListener(new MouseAdapter() {

			@Override
			public void mouseClicked(MouseEvent e) {
				System.out.println("Save query");
				JFileChooser fc = new JFileChooser();
		        fc.setCurrentDirectory(new File(File.listRoots()[0].getPath()));		        
		        fc.showSaveDialog(null);
		        File selFile = fc.getSelectedFile();
		        if(selFile!= null){
		        	String text = txtAreaQueryEditor.getText();
		        	String[] textArr = text.split("\n");
		        	FileWriter fw;
					try {
						fw = new FileWriter(selFile);
						BufferedWriter bf = new BufferedWriter(fw);
						for(int i =0; i< textArr.length; i++){
							bf.write(textArr[i]);
							bf.newLine();
						}			        	
			        	bf.close();
					} catch (IOException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								e1.getMessage());
					}
		        	
		        }
			}
		});
		toolBar.add(btnSave);

		btnExecuteQuery.setToolTipText("Execute query");
		btnExecuteQuery.setIcon(new ImageIcon(DBMS_Client.class
				.getResource("images/execute-query-icon.png")));
		btnExecuteQuery.addMouseListener(new MouseAdapter() {

			@Override
			public void mouseClicked(MouseEvent e) {
				System.out.println("Execute query");
				String query = txtAreaQueryEditor.getText();
				try {
					executeQuery(query);
					// add to output panel
					addOutputItem(query);
				} catch (Exception e1) {
					System.out.println(e1.getMessage());
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							e1.getMessage());
				}
			}
		});

		toolBar.add(btnExecuteQuery);

		splitPane.setResizeWeight(0.35);
		contentPane.add(splitPane, BorderLayout.CENTER);
		splitPane_1.setResizeWeight(0.75);
		splitPane_1.setOrientation(JSplitPane.VERTICAL_SPLIT);
		splitPane.setLeftComponent(splitPane_1);

		// Load all database and show to tree
		InitiliazeController initCtrl = new InitiliazeController();
		ArrayList<DatabaseIconDTO> dbs = initCtrl.getAllDatabaseName();
		DefaultMutableTreeNode nodeDBs = new DefaultMutableTreeNode();
		if (dbs != null) {
			nodeDBs = UISupport.processHierarchyTreeNode(dbs.toArray());
		}

		JScrollPane scrollPane_1 = new JScrollPane();
		splitPane_1.setLeftComponent(scrollPane_1);
		treeDBs = new JTree(nodeDBs);
		scrollPane_1.setViewportView(treeDBs);

		treeDBs.addMouseListener(new MouseAdapter() {
			private void dbsPopup(MouseEvent e) {
				int x = e.getX();
				int y = e.getY();
				final JTree tree = (JTree) e.getSource();
				final TreePath path = tree.getPathForLocation(x, y);
				if (path == null)
					return;
				tree.setSelectionPath(path);
				if (path.getPathCount() == 2) {
					// popup for database's menu
					PopupMenuDatabase popup = new PopupMenuDatabase();
					popup.addPopupDatabaseEventListener(new IPopupMenuDatabaseEventListener() {

						@Override
						public void setAsDefaultDatabase(DatabasePopupEvent e) {
							System.out.println("set as default");
							DefaultMutableTreeNode dbnode = (DefaultMutableTreeNode) path
									.getLastPathComponent();
							// unset default to current default database
							defaultDatabase.setAsDefault(false);
							defaultDatabase = (DatabaseIconDTO) dbnode
									.getUserObject();
							DatabaseController dbCtrl;
							try {
								dbCtrl = new DatabaseController(defaultDatabase
										.getDatabaseName());
							} catch (UnknownHostException e2) {
								// TODO Auto-generated catch block
								e2.printStackTrace();
								JOptionPane.showMessageDialog(
										tabbedPaneQueries, e2.getMessage());
								return;

							} catch (IOException e2) {
								// TODO Auto-generated catch block
								e2.printStackTrace();
								JOptionPane.showMessageDialog(
										tabbedPaneQueries, e2.getMessage());
								return;
							}
							try {
								dbCtrl.setAsDefault();
								// set default database tree node
								defaultDBNode = dbnode;
							} catch (Exception e1) {
								JOptionPane.showMessageDialog(
										tabbedPaneQueries, e1.getMessage());
							}
							// set default to selected database
							defaultDatabase.setAsDefault(true);
							tree.setCellRenderer(new DBMSTreeCellRenderer());
						}

						@Override
						public void refreshAll(DatabasePopupEvent e) {
							// TODO Auto-generated method stub

						}

						@Override
						public void dropDatabase(DatabasePopupEvent e) {
							DefaultMutableTreeNode dbnode = (DefaultMutableTreeNode) tree
									.getLastSelectedPathComponent();
							String dbname = ((DatabaseIconDTO) dbnode
									.getUserObject()).getDatabaseName();
							try {
								DatabaseController ctrl = new DatabaseController(
										dbname);
								if (ctrl.dropDatabase() == true) {
									DefaultMutableTreeNode rootNode = (DefaultMutableTreeNode) dbnode
											.getParent();
									rootNode.remove(dbnode);
									reloadDatabaseToTreeView();
									JOptionPane.showMessageDialog(
											tabbedPaneQueries,
											"Drop successful");
								}
							} catch (Exception e2) {
								JOptionPane.showMessageDialog(
										tabbedPaneQueries, "Drop unsuccessful");
							}
						}

						@Override
						public void alterDatabase(DatabasePopupEvent e) {
						}
					});
					popup.show(tree, x, y);
				} else {
					// popup for table's menu
					PopupMenuTable popup = new PopupMenuTable();
					popup.addPopupTableEventListener(new IPopupMenuTableEventListener() {

						@Override
						public void select200Rows(TablePopupEvent e) {
							select200FirstRows(tree);
						}

						@Override
						public void refreshAll(TablePopupEvent e) {
							// TODO Auto-generated method stub

						}

						@Override
						public void dropTable(TablePopupEvent e) {
							DefaultMutableTreeNode tblNode = (DefaultMutableTreeNode) tree
									.getLastSelectedPathComponent();
							String tableName = ((TableIconDTO) tblNode
									.getUserObject()).getTableName();
							DefaultMutableTreeNode dbNode = (DefaultMutableTreeNode) tblNode
									.getParent();
							String dbname = ((DatabaseIconDTO) dbNode
									.getUserObject()).getDatabaseName();
							try {
								TableController ctrl = new TableController(
										dbname, tableName);
								if (ctrl.dropTable() == true) {
									DefaultTreeModel model = (DefaultTreeModel) tree
											.getModel();
									model.removeNodeFromParent(tblNode);
									JOptionPane.showMessageDialog(
											tabbedPaneQueries,
											"Drop successful");
								}
							} catch (Exception e2) {
								JOptionPane.showMessageDialog(
										tabbedPaneQueries, "Drop unsuccessful");
							}
						}

						@Override
						public void createTable(TablePopupEvent e) {
							// TODO Auto-generated method stub

						}

						@Override
						public void alterTable(TablePopupEvent e) {
							// TODO Auto-generated method stub

						}

						@Override
						public void edit200Rows(TablePopupEvent e) {
							editTableData(tree);
						}
					});
					popup.show(tree, x, y);
				}
			}

			@Override
			public void mouseClicked(MouseEvent e) {
				if (e.isPopupTrigger()) {
					dbsPopup(e);
				} else {
					int x = e.getX();
					int y = e.getY();
					JTree tree = (JTree) e.getSource();
					TreePath path = tree.getPathForLocation(x, y);
					if (path == null)
						return;
					DefaultMutableTreeNode node = (DefaultMutableTreeNode) tree
							.getLastSelectedPathComponent();
					if (path.getPathCount() == 2) {
						/*
						 * GET TABLE FROM DATABASE
						 */
						reloadTableToTreeView(node);
					} else if (path.getPathCount() == 3) {
						// load columns
						reloadColumnToTreeView(node);
					}

					tree.setSelectionPath(path);
				}
			}

			@Override
			public void mousePressed(MouseEvent e) {
				if (e.isPopupTrigger()) {
					dbsPopup(e);
				}
			}

			@Override
			public void mouseReleased(MouseEvent e) {
				if (e.isPopupTrigger()) {
					dbsPopup(e);
				}
			}
		});
		treeDBs.setRootVisible(false);
		treeDBs.setCellRenderer(new DBMSTreeCellRenderer());

		JScrollPane scrollPane_2 = new JScrollPane();
		splitPane_1.setRightComponent(scrollPane_2);
		scrollPane_2.setViewportView(textArea);

		textArea.setBackground(new Color(224, 255, 255));

		splitPane_2.setResizeWeight(0.7);
		splitPane_2.setOrientation(JSplitPane.VERTICAL_SPLIT);
		splitPane.setRightComponent(splitPane_2);

		splitPane_2.setLeftComponent(tabbedPaneQueries);

		tabbedPaneQueries.addTab("Query 1", null, panelQuery1, null);
		panelQuery1.setLayout(new BorderLayout(0, 0));

		JScrollPane scrollPane = new JScrollPane();
		panelQuery1.add(scrollPane, BorderLayout.CENTER);

		// Text area query editor
		scrollPane.setViewportView(txtAreaQueryEditor);

		splitPane_2.setRightComponent(tabbedPaneOutput);

		tabbedPaneOutput.addTab("Result", null, tabResult, null);
		tabResult.setLayout(new BorderLayout(0, 0));

		toolbarResult.setFloatable(false);
		toolbarResult.setVisible(false);
		tabResult.add(toolbarResult, BorderLayout.NORTH);

		JButton btnSaveResult = new JButton("");
		btnSaveResult.addMouseListener(new MouseAdapter() {
			@Override
			// SAVE DATA TABLE IF IT HAS ANY CHANGE.
			public void mouseClicked(MouseEvent arg0) {
				DefaultTableModel model = (DefaultTableModel) tblResult
						.getModel();
				int numCols = model.getColumnCount();
				int numRows = model.getRowCount();
				System.out.println(numCols + ", " + numRows);

				ArrayList<RowDTO> rowsDTO = new ArrayList<RowDTO>();
				Vector<Object> dataModel = model.getDataVector();
				Iterator<Object> iter = dataModel.iterator();
				while (iter.hasNext()) {
					@SuppressWarnings("unchecked")
					Vector<Object> row = (Vector<Object>) iter.next();
					if (!isNullRow(row)) {
						RowDTO info = new RowDTO(row);
						rowsDTO.add(info);
					}
				}

				// Insert rows to database.
				try {
					TableController tblCtrl = new TableController(
							DBMS_Client.databaseName, DBMS_Client.tableName);
					if (tblCtrl.edit200Rows(rowsDTO)) {
						JOptionPane.showMessageDialog(tabbedPaneQueries,
								"Saving successful");
						tblResult.setModel(new DefaultTableModel());
						toolbarResult.setVisible(false);
					}
				} catch (Exception e) {
					JOptionPane.showMessageDialog(tabbedPaneQueries,
							"Saving failed.\n" + e.getMessage());
				}
			}
		});
		btnSaveResult.setIcon(new ImageIcon(DBMS_Client.class
				.getResource("images/save-mini-icon.png")));
		toolbarResult.add(btnSaveResult);

		JButton btnRevert = new JButton("");
		btnRevert.addMouseListener(new MouseAdapter() {
			@Override
			// REVERT CHANGES FROM TABLE DATA RESULT.
			public void mouseClicked(MouseEvent e) {
				tblResult.setModel(new DefaultTableModel());
				toolbarResult.setVisible(false);
			}
		});
		btnRevert.setIcon(new ImageIcon(DBMS_Client.class
				.getResource("images/revert-icon.png")));
		toolbarResult.add(btnRevert);

		JScrollPane scrollPane_3 = new JScrollPane();
		tabResult.add(scrollPane_3, BorderLayout.CENTER);

		tblResult = new JTable();
		tblResult.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		// Set Key listener for 2nd visible column
		Action action = new AbstractAction() {
			@Override
			public void actionPerformed(ActionEvent arg0) {
				TableCellListener tcl = (TableCellListener) arg0.getSource();
				if (tcl.getColumn() == 0) {
					// add new row
					DefaultTableModel model = (DefaultTableModel) tblResult
							.getModel();
					model.insertRow(tcl.getRow() + 1, new Object[] { null,
							null, null });
				}
			}
		};
		@SuppressWarnings("unused")
		TableCellListener tcl = new TableCellListener(tblResult, action);

		scrollPane_3.setViewportView(tblResult);

		tabbedPaneOutput.addTab("Output", null, tabOutput, null);
		tabOutput.setLayout(new BorderLayout(0, 0));

		JScrollPane scrollPane_4 = new JScrollPane();
		tabOutput.add(scrollPane_4, BorderLayout.CENTER);

		scrollPane_4.setViewportView(listOutput);

		// Create database
		btnCreateDatabase.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseClicked(MouseEvent e) {
				// Button Create database's clicked.
				final PanelCreateDatabase panelCreateDatabase = new PanelCreateDatabase();
				panelCreateDatabase
						.addCreateDatabaseEventListener(new ICreateDatabaseEventListener() {
							@Override
							public void cancel(CreateDatabaseEvent e) {
								tabbedPaneQueries.remove(panelCreateDatabase);
							}

							@Override
							public void apply(CreateDatabaseEvent e) {
								// Create database with name
								DatabaseController ctrl = null;
								try {
									ctrl = new DatabaseController(
											panelCreateDatabase
													.getDatbaseName());
								} catch (UnknownHostException e2) {
									// TODO Auto-generated catch block
									e2.printStackTrace();
									JOptionPane.showMessageDialog(
											tabbedPaneQueries, e2.getMessage());
									return;
								} catch (IOException e2) {
									// TODO Auto-generated catch block
									e2.printStackTrace();
									JOptionPane.showMessageDialog(
											tabbedPaneQueries, e2.getMessage());
									return;
								}
								boolean result;
								try {
									result = ctrl.createDatabase();
									if (result) {
										JOptionPane.showMessageDialog(
												panelCreateDatabase,
												"Create successful");
										tabbedPaneQueries
												.remove(panelCreateDatabase);
										reloadDatabaseToTreeView();
									}
								} catch (Exception e1) {
									e1.printStackTrace();
									JOptionPane.showMessageDialog(
											panelCreateDatabase,
											e1.getMessage());
								}
							}
						});
				panelCreateDatabase.setVisible(true);
				tabbedPaneQueries.addTab("Create database", null,
						panelCreateDatabase, "Create database");
				tabbedPaneQueries.setSelectedComponent(panelCreateDatabase);

			}
		});
	}

	/**
	 * Execute query when Execute query button's clicked.
	 * 
	 * @param query
	 * @throws Exception
	 */
	private void executeQuery(String query) throws Exception {
		QueryController ctrl = new QueryController(query);
		EQueryType type = ctrl.executeQuery();
		if (type == null) {
			System.out.println("Invalid query statement");
			addOutputItem("Invalid query statement");
			return;
		}
		switch (type) {
		case CREATE_DATABSE:
		case DROP_DATABASE:
			// reload all database to tree view
			reloadDatabaseToTreeView();
			break;
		case CREATE_TABLE:
		case DROP_TABLE:
			reloadTableToTreeView(defaultDBNode);
			treeDBs.expandPath(new TreePath(defaultDBNode.getPath()));
			break;
		case INSERT:
			break;
		default:
			break;
		}
	}

	/**
	 * Reload all databases to tree view treeDBs
	 */
	private void reloadDatabaseToTreeView() {
		InitiliazeController initCtrl = null;
		try {
			initCtrl = new InitiliazeController();
		} catch (UnknownHostException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			JOptionPane.showMessageDialog(tabbedPaneQueries, e.getMessage());
			return;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ArrayList<DatabaseIconDTO> dbs;
		try {
			dbs = initCtrl.getAllDatabaseName();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			JOptionPane.showMessageDialog(tabbedPaneQueries, e.getMessage());
			return;
		}
		DefaultMutableTreeNode nodeDBs = UISupport.processHierarchyTreeNode(dbs
				.toArray());
		treeDBs.setModel(new DefaultTreeModel(nodeDBs));
	}

	/**
	 * Reload all table of selected database to tree view
	 * 
	 * @param dbnode
	 */
	private void reloadTableToTreeView(DefaultMutableTreeNode dbnode) {
		DatabaseIconDTO dbIcon = (DatabaseIconDTO) dbnode.getUserObject();
		String dbname = dbIcon.getDatabaseName();
		if (dbname.isEmpty()) {
			return;
		}
		DatabaseController dbCtrl = null;
		try {
			dbCtrl = new DatabaseController(dbname);
		} catch (UnknownHostException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			JOptionPane.showMessageDialog(tabbedPaneQueries, e.getMessage());
			return;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			JOptionPane.showMessageDialog(tabbedPaneQueries, e.getMessage());
			return;
		}
		try {
			// Get table name
			ArrayList<TableIconDTO> tableNames = dbCtrl.getAllTableName();
			if (tableNames == null)
				return;
			// Remove all child node of dbnode
			dbnode.removeAllChildren();
			// Add to treeDBs
			for (TableIconDTO table : tableNames) {
				dbnode.add(new DefaultMutableTreeNode(table));

				// Reload node
				DefaultTreeModel model = (DefaultTreeModel) treeDBs.getModel();
				model.reload(dbnode);
			}
		} catch (Exception e2) {
			JOptionPane.showMessageDialog(tabbedPaneQueries, e2.getMessage());
		}
	}

	/**
	 * Reload columns on a table node.
	 * 
	 * @param tblNode
	 */
	private void reloadColumnToTreeView(DefaultMutableTreeNode tblNode) {
		// 1. Get db name
		DefaultMutableTreeNode dbnode = (DefaultMutableTreeNode) tblNode
				.getParent();
		DatabaseIconDTO dbIcon = (DatabaseIconDTO) dbnode.getUserObject();
		String dbname = dbIcon.getDatabaseName();

		// 2. Get tbl name
		TableIconDTO tblIcon = (TableIconDTO) tblNode.getUserObject();
		String tblName = tblIcon.getTableName();
		TableController tblCtrl;
		try {
			tblCtrl = new TableController(dbname, tblName);
		} catch (UnknownHostException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			JOptionPane.showMessageDialog(tabbedPaneQueries, e.getMessage());
			return;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			JOptionPane.showMessageDialog(tabbedPaneQueries, e.getMessage());
			return;
		}
		try {
			// Get table name
			ArrayList<ColumnDTO> columns = tblCtrl.getTableColumns();
			if (columns == null) {
				return;
			}

			// Remove all child node of dbnode
			tblNode.removeAllChildren();
			// Add to treeDBs
			for (ColumnDTO columnDTO : columns) {
				tblNode.add(new DefaultMutableTreeNode(columnDTO
						.castToColumnIconDTO()));

				// Reload tblNode
				DefaultTreeModel model = (DefaultTreeModel) treeDBs.getModel();
				model.reload(tblNode);
			}
		} catch (Exception e2) {
			JOptionPane.showMessageDialog(tabbedPaneQueries, e2.getMessage());
		}
	}

	/**
	 * Check is null row data
	 * 
	 * @param row
	 *            row data
	 * @return
	 */
	private boolean isNullRow(Vector<Object> row) {
		Iterator<Object> i = row.iterator();
		while (i.hasNext()) {
			Object object = (Object) i.next();
			if (object != null) {
				if (!((String) object).isEmpty()) {
					return false;
				}
			}
		}
		return true;
	}

	/**
	 * Select 200 first rows and show it.
	 * 
	 * @param tree
	 */
	private void select200FirstRows(JTree tree) {
		DefaultMutableTreeNode tblNode = (DefaultMutableTreeNode) tree
				.getLastSelectedPathComponent();
		String tableName = ((TableIconDTO) tblNode.getUserObject())
				.getTableName();
		DefaultMutableTreeNode dbNode = (DefaultMutableTreeNode) tblNode
				.getParent();
		String dbname = ((DatabaseIconDTO) dbNode.getUserObject())
				.getDatabaseName();

		// Cache database name and table name to save after edit.
		DBMS_Client.databaseName = dbname;
		DBMS_Client.tableName = tableName;

		try {
			TableController ctrl = new TableController(dbname, tableName);
			ArrayList<ColumnDTO> columns = ctrl.getTableColumns();
			if (columns != null) {
				DefaultTableModel model = new DefaultTableModel();
				Vector<String> colHeader = new Vector<>(columns.size());
				for (ColumnDTO columnDTO : columns) {
					colHeader.add(columnDTO.getName());
				}
				model.setColumnIdentifiers(colHeader);

				// Get 200 first rows of table data if exists.
				ArrayList<RowDTO> rows = ctrl.select200FirstRow();
				if (rows != null) {
					for (RowDTO row : rows) {
						model.addRow(row.getValue().toArray());
					}
				}

				tblResult.setModel(model);
				tabbedPaneOutput.setSelectedComponent(tabResult);
			}

			// Show result toolbar
			// toolbarResult.setVisible(true);
		} catch (Exception e2) {
			JOptionPane.showMessageDialog(tabbedPaneQueries,
					"Table structure is invalid");
		}
	}

	/**
	 * edit table data
	 * 
	 * @param tree
	 *            treeDBs
	 */
	private void editTableData(final JTree tree) {
		DefaultMutableTreeNode tblNode = (DefaultMutableTreeNode) tree
				.getLastSelectedPathComponent();
		String tableName = ((TableIconDTO) tblNode.getUserObject())
				.getTableName();
		DefaultMutableTreeNode dbNode = (DefaultMutableTreeNode) tblNode
				.getParent();
		String dbname = ((DatabaseIconDTO) dbNode.getUserObject())
				.getDatabaseName();

		// Cache database name and table name to save after edit.
		DBMS_Client.databaseName = dbname;
		DBMS_Client.tableName = tableName;

		try {
			TableController ctrl = new TableController(dbname, tableName);
			ArrayList<ColumnDTO> columns = ctrl.getTableColumns();
			if (columns != null) {
				DefaultTableModel model = new DefaultTableModel();
				Vector<String> colHeader = new Vector<>(columns.size());
				for (ColumnDTO columnDTO : columns) {
					colHeader.add(columnDTO.getName());
				}
				model.setColumnIdentifiers(colHeader);

				// Get 200 first rows of table data if exists.
				ArrayList<RowDTO> rows = ctrl.select200FirstRow();
				if (rows != null) {
					for (RowDTO row : rows) {
						model.addRow(row.getValue().toArray());
					}
				}
				// add new row
				model.addRow(new Object[] { null });

				tblResult.setModel(model);
				tabbedPaneOutput.setSelectedComponent(tabResult);
			}

			// Show result toolbar
			toolbarResult.setVisible(true);
		} catch (Exception e2) {
			JOptionPane.showMessageDialog(tabbedPaneQueries,
					"Table structure is invalid");
		}
	}

	/**
	 * Add item to list view
	 * 
	 * @param item
	 *            Object
	 */
	private void addOutputItem(Object item) {
		listOutputModel.addElement(item);
		listOutput.setModel(listOutputModel);
		tabbedPaneOutput.setSelectedComponent(tabOutput);
	}
}
